﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Common;

namespace Ex6.UsingSMO
{
    public class GettingStartedWithSMO
    {
        //public static void Main()
        //{
        //    Server myServer = new Server(@".\SQLEXPRESS");
        //    try
        //    {
        //        myServer.ConnectionContext.LoginSecure = true;
        //        myServer.ConnectionContext.Connect();

        //        //BackupDatabaseFull(myServer, myServer.Databases["Northwind"]);
        //        RestoreDatabaseWithDifferentNameAndLocation(myServer, myServer.Databases["Northwind"]);
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //        Console.WriteLine(ex.InnerException.Message);
        //    }
        //    finally
        //    {
        //        if (myServer.ConnectionContext.IsOpen)
        //            myServer.ConnectionContext.Disconnect();
        //    }
        //}

        //Backup and Restore SQL Server databases programmatically with SMO

        private static void BackupDatabaseFull(Server myServer, Database myDatabase)
        {
            Backup bkpDBFull = new Backup();

            /* Specify whether you want to back up database or files or log */
            bkpDBFull.Action = BackupActionType.Database;
            
            /* Specify the name of the database to back up */
            bkpDBFull.Database = myDatabase.Name;
            
            /* You can take backup on several media type (disk or tape), here I am using
             * File type and storing backup on the file system */
            bkpDBFull.Devices.AddDevice(@"D:\NorthwindFull.bak", DeviceType.File);
            bkpDBFull.BackupSetName = "Northwind database Backup";
            bkpDBFull.BackupSetDescription = "Northwind database - Full Backup";

            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10);
            /* You can specify Initialize = false (default) to create a new 
             * backup set which will be appended as last backup set on the media. You can
             * specify Initialize = true to make the backup as first set on the mediuam and
             * to overwrite any other existing backup sets if the all the backup sets have
             * expired and specified backup set name matches with the name on the medium */
            bkpDBFull.Initialize = false;

            /* Wiring up events for progress monitoring */
            bkpDBFull.PercentComplete += CompletionStatusInPercent;
            bkpDBFull.Complete += Backup_Completed;

            /* SqlBackup method starts to take back up You can also use SqlBackupAsync method 
             * to perform backup operation asynchronously */
            bkpDBFull.SqlBackup(myServer);
        }
        
        private static void BackupDatabaseDifferential(Server myServer, Database myDatabase)
        {
            Backup bkpDBDifferential = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBDifferential.Action = BackupActionType.Database;
            /* Specify the name of the database to back up */
            bkpDBDifferential.Database = myDatabase.Name;
            /* You can take backup on several media type (disk or tape), here I am using
             * File type and storing backup on the file system */
            bkpDBDifferential.Devices.AddDevice(@"D:\NorthwindDifferential.bak", DeviceType.File);
            bkpDBDifferential.BackupSetName = "Northwind database Backup";
            bkpDBDifferential.BackupSetDescription = "Northwind database - Differential Backup";
            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBDifferential.ExpirationDate = DateTime.Today.AddDays(10);
            /* You can specify Initialize = false (default) to create a new 
             * backup set which will be appended as last backup set on the media. You can
             * specify Initialize = true to make the backup as first set on the mediuam and
             * to overwrite any other existing backup sets if the all the backup sets have
             * expired and specified backup set name matches with the name on the medium */
            bkpDBDifferential.Initialize = false;
            /* You can specify Incremental = false (default) to perform full backup
             * or Incremental = true to perform differential backup since most recent
             * full backup */
            bkpDBDifferential.Incremental = true;
            /* Wiring up events for progress monitoring */
            bkpDBDifferential.PercentComplete += CompletionStatusInPercent;
            bkpDBDifferential.Complete += Backup_Completed;
            /* SqlBackup method starts to take back up
             * You cab also use SqlBackupAsync method to perform backup 
             * operation asynchronously */
            bkpDBDifferential.SqlBackup(myServer);
        }
        
        private static void BackupDatabaseLog(Server myServer, Database myDatabase)
        {
            Backup bkpDBLog = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBLog.Action = BackupActionType.Log;
            /* Specify the name of the database to back up */
            bkpDBLog.Database = myDatabase.Name;
            /* You can take backup on several media type (disk or tape), here I am using
             * File type and storing backup on the file system */
            bkpDBLog.Devices.AddDevice(@"D:\NorthwindLog.bak", DeviceType.File);
            bkpDBLog.BackupSetName = "Northwind database Backup";
            bkpDBLog.BackupSetDescription = "Northwind database - Log Backup";
            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBLog.ExpirationDate = DateTime.Today.AddDays(10);
            /* You can specify Initialize = false (default) to create a new 
             * backup set which will be appended as last backup set on the media. You can
             * specify Initialize = true to make the backup as first set on the mediuam and
             * to overwrite any other existing backup sets if the all the backup sets have
             * expired and specified backup set name matches with the name on the medium */
            bkpDBLog.Initialize = false;
            /* Wiring up events for progress monitoring */
            bkpDBLog.PercentComplete += CompletionStatusInPercent;
            bkpDBLog.Complete += Backup_Completed;
            /* SqlBackup method starts to take back up
             * You cab also use SqlBackupAsync method to perform backup 
             * operation asynchronously */
            bkpDBLog.SqlBackup(myServer);
        }
        
        private static void BackupDatabaseFullWithCompression(Server myServer, Database myDatabase)
        {
            Backup bkpDBFullWithCompression = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBFullWithCompression.Action = BackupActionType.Database;
            /* Specify the name of the database to back up */
            bkpDBFullWithCompression.Database = myDatabase.Name;
            /* You can use back up compression technique of SQL Server 2008,
             * specify CompressionOption property to On for compressed backup */
            bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.On;
            bkpDBFullWithCompression.Devices.AddDevice(@"D:\NorthwindFullWithCompression.bak", DeviceType.File);
            bkpDBFullWithCompression.BackupSetName = "Northwind database Backup - Compressed";
            bkpDBFullWithCompression.BackupSetDescription = "Northwind database - Full Backup with Compressin - only in SQL Server 2008";
            bkpDBFullWithCompression.SqlBackup(myServer);
        }
        
        private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args)
        {
            Console.Clear();
            Console.WriteLine("Percent completed: {0}%.", args.Percent);
        }
        
        private static void Backup_Completed(object sender, ServerMessageEventArgs args)
        {
            Console.WriteLine("Hurray...Backup completed." );
            Console.WriteLine(args.Error.Message);
        }
        
        private static void Restore_Completed(object sender, ServerMessageEventArgs args)
        {
            Console.WriteLine("Hurray...Restore completed.");
            Console.WriteLine(args.Error.Message);
        }
        
        private static void RestoreDatabase(Server myServer, Database myDatabase)
        {
            Restore restoreDB = new Restore();
            restoreDB.Database = myDatabase.Name;

            /* Specify whether you want to restore database or files or log etc */
            restoreDB.Action = RestoreActionType.Database;
            restoreDB.Devices.AddDevice(@"D:\NorthwindFull.bak", DeviceType.File);
            
            /* You can specify ReplaceDatabase = false (default) to not create a new image
             * of the database, the specified database must exist on SQL Server instance.
             * If you can specify ReplaceDatabase = true to create new database image 
             * regardless of the existence of specified database with same name */
            restoreDB.ReplaceDatabase = true;

            /* If you have differential or log restore to be followed, you would need
             * to specify NoRecovery = true, this will ensure no recovery is done after the 
             * restore and subsequent restores are allowed. It means it will database
             * in the Restoring state. */
            restoreDB.NoRecovery = true;
            
            restoreDB.PercentComplete += CompletionStatusInPercent;
            restoreDB.Complete += Restore_Completed;

            restoreDB.SqlRestore(myServer);
        }
        
        private static void RestoreDatabaseLog(Server myServer, Database myDatabase)
        {
            Restore restoreDBLog = new Restore();
            restoreDBLog.Database = myDatabase.Name;
            restoreDBLog.Action = RestoreActionType.Log;
            restoreDBLog.Devices.AddDevice(@"D:\NorthwindLog.bak", DeviceType.File);
            /* You can specify NoRecovery = false (default) so that transactions are
             * rolled forward and recovered. */
            restoreDBLog.NoRecovery = false;
            /* Wiring up events for progress monitoring */
            restoreDBLog.PercentComplete += CompletionStatusInPercent;
            restoreDBLog.Complete += Restore_Completed;
            /* SqlRestore method starts to restore database
             * You cab also use SqlRestoreAsync method to perform restore 
             * operation asynchronously */
            restoreDBLog.SqlRestore(myServer);
        }
        
        private static void RestoreDatabaseWithDifferentNameAndLocation(Server myServer, Database myDatabase)
        {
            Restore restoreDB = new Restore();
            restoreDB.Database = myDatabase.Name+"New";
            restoreDB.Action = RestoreActionType.Database;
            restoreDB.Devices.AddDevice(@"D:\NorthwindFull.bak", DeviceType.File);

            restoreDB.ReplaceDatabase = true;
            restoreDB.NoRecovery = true;

            restoreDB.RelocateFiles.Add(new RelocateFile("northwnd.mdf", @"D:\NorthwindNew_Data.mdf"));
            restoreDB.RelocateFiles.Add(new RelocateFile("northwnd_log.ldf", @"D:\NorthwindNew_Log.ldf"));
            
            
            restoreDB.PercentComplete += CompletionStatusInPercent;
            restoreDB.Complete += Restore_Completed;

            restoreDB.SqlRestore(myServer);
        }
    
        //Generating scripts of DB, Table, etc.

        private static void GenerateTableScript(Server myServer)
        {
            Scripter scripter = new Scripter(myServer);
            Database myAdventureWorks = myServer.Databases["Northwind"];
            Table myTable = myAdventureWorks.Tables["Employees"];

            /* Generate Scripts of table along with for all
             * objects on which this table depends on */
            ScriptingOptions scriptOptionsForDependendencies = new ScriptingOptions();
            scriptOptionsForDependendencies.WithDependencies = true;
            /* DriAll will include all DRI objects in the generated script. */
            scriptOptionsForDependendencies.DriAll = true;
            /* You can optionally choose each DRI object separately as given below */
            //scriptOptionsForDependendencies.DriAllConstraints = true;
            //scriptOptionsForDependendencies.DriAllKeys = true;
            //scriptOptionsForDependendencies.DriChecks = true;
            //scriptOptionsForDependendencies.DriClustered = true;
            //scriptOptionsForDependendencies.DriDefaults = true;
            //scriptOptionsForDependendencies.DriForeignKeys = true;
            //scriptOptionsForDependendencies.DriIndexes = true;
            //scriptOptionsForDependendencies.DriNonClustered = true;
            //scriptOptionsForDependendencies.DriPrimaryKey = true;
            //scriptOptionsForDependendencies.DriUniqueKeys = true;

            /* If you can use FileName to output generated script in a file 
             * Note : You need to have access on the specified location*/
            scriptOptionsForDependendencies.FileName = "../../TableScriptWithDependencies.sql";
            StringCollection tableScripts = myTable.Script(scriptOptionsForDependendencies);
            foreach (string script in tableScripts)
                Console.WriteLine(script);
        }

        private static void GeneratingDatabaseScript(Server myServer)
        {
            Scripter scripter = new Scripter(myServer);

            Database myNorthwindDatabase = myServer.Databases["Northwind"];
            Database myNewDatabase = myServer.Databases["MyNewDatabase"];

            Urn[] DatabaseURNs = new Urn[] { myNorthwindDatabase.Urn, myNewDatabase.Urn };
            StringCollection scriptCollection = scripter.Script(DatabaseURNs);
            foreach (string script in scriptCollection)
                Console.WriteLine(script);
        }

        private static void CreatingNewDB(Server myServer)
        {
            //Drop the database if it exists
            if (myServer.Databases["MyNewDatabase"] != null)
                myServer.Databases["MyNewDatabase"].Drop();

            //Create database called, "MyNewDatabase"
            Database myDatabase = new Database(myServer, "MyNewDatabase");
            myDatabase.Create();

            //Create a table instance
            Table myEmpTable = new Table(myDatabase, "MyEmpTable");

            //Add [EmpID] column to created table instance
            Column empID = new Column(myEmpTable, "EmpID", DataType.Int);
            empID.Identity = true;
            myEmpTable.Columns.Add(empID);

            //Add another column [EmpName] to created table instance
            Column empName = new Column(myEmpTable, "EmpName", DataType.VarChar(200));
            empName.Nullable = true;
            myEmpTable.Columns.Add(empName);

            //Add third column [DOJ] to created table instance with default constraint
            Column DOJ = new Column(myEmpTable, "DOJ", DataType.DateTime);
            DOJ.AddDefaultConstraint(); // you can specify constraint name here as well
            DOJ.DefaultConstraint.Text = "GETDATE()";
            myEmpTable.Columns.Add(DOJ);

            // Add primary key index to the table
            Index primaryKeyIndex = new Index(myEmpTable, "PK_MyEmpTable");
            primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
            primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, "EmpID"));
            myEmpTable.Indexes.Add(primaryKeyIndex);

            //Unless you call create method, table will not created on the server 
            myEmpTable.Create();
        }

        private static void RetrievingDatabasesAndTables(Server myServer)
        {
            Console.WriteLine("\nListing all DBs in Server: \n");
            foreach (Database myDatabase in myServer.Databases)
            {
                Console.WriteLine(myDatabase.Name);
            }

            Console.WriteLine(new string('-', 25));
            Database myNorthwindDB = myServer.Databases["Northwind"];

            Console.WriteLine("\nListing all tables in NorthwindDB:\n");
            foreach (Table table in myNorthwindDB.Tables)
            {
                Console.WriteLine(table.Name);
            }

            Console.WriteLine("\nListing properties of table Employees:\n");
            foreach (Property myTableProperty in myNorthwindDB.Tables["Employees"].Properties)
            {
                Console.WriteLine(myTableProperty.Name + " " + myTableProperty.Value);
            }
        }

        private static void GenerateTableScriptWithIndexes(Server myServer)
        {
            Scripter scripter = new Scripter(myServer);
            Database myAdventureWorks = myServer.Databases["Northwind"];
            /* With ScriptingOptions you can specify different scripting
             * options, for example to include IF NOT EXISTS, DROP
             * statements, output location etc*/
            ScriptingOptions scriptOptions = new ScriptingOptions();
            scriptOptions.ScriptDrops = true;
            scriptOptions.IncludeIfNotExists = true;
            foreach (Table myTable in myAdventureWorks.Tables)
            {
                /* Generating IF EXISTS and DROP command for tables */
                StringCollection tableScripts = myTable.Script(scriptOptions);
                foreach (string script in tableScripts)
                    Console.WriteLine(script);
                /* Generating CREATE TABLE command */
                tableScripts = myTable.Script();
                foreach (string script in tableScripts)
                    Console.WriteLine(script);
                IndexCollection indexCol = myTable.Indexes;
                foreach (Index myIndex in myTable.Indexes)
                {
                    /* Generating IF EXISTS and DROP command for table indexes */
                    StringCollection indexScripts = myIndex.Script(scriptOptions);
                    foreach (string script in indexScripts)
                        Console.WriteLine(script);
                    /* Generating CREATE INDEX command for table indexes */
                    indexScripts = myIndex.Script();
                    foreach (string script in indexScripts)
                        Console.WriteLine(script);
                }
            }
        }

        private static void GenerateScriptWithoutCreatingObjectOnServer(Server myServer)
        {
            /* Drop the database if it exists */
            if (myServer.Databases["MyNewDatabase"] != null)
                myServer.Databases["MyNewDatabase"].Drop();
            /* Create database called, "MyNewDatabase" */
            Database myDatabase = new Database(myServer, "MyNewDatabase");
            /* Output the database script on the console */
            StringCollection DBScripts = myDatabase.Script();
            foreach (string script in DBScripts)
                Console.WriteLine(script);
            /* Create a table instance */
            Table myEmpTable = new Table(myDatabase, "MyEmpTable");
            /* Add [EmpID] column to created table instance */
            Column empID = new Column(myEmpTable, "EmpID", DataType.Int);
            empID.Identity = true;
            myEmpTable.Columns.Add(empID);
            /* Add another column [EmpName] to created table instance */
            Column empName = new Column(myEmpTable, "EmpName", DataType.VarChar(200));
            empName.Nullable = true;
            myEmpTable.Columns.Add(empName);
            /* Add third column [DOJ] to created table instance with default constraint */
            Column DOJ = new Column(myEmpTable, "DOJ", DataType.DateTime);
            DOJ.AddDefaultConstraint(); // you can specify constraint name here as well
            DOJ.DefaultConstraint.Text = "GETDATE()";
            myEmpTable.Columns.Add(DOJ);
            /* Add primary key index to the table */
            Index primaryKeyIndex = new Index(myEmpTable, "PK_MyEmpTable");
            primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
            primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, "EmpID"));
            myEmpTable.Indexes.Add(primaryKeyIndex);
            /* Output the table script on the console */
            StringCollection TableScripts = myEmpTable.Script();
            foreach (string script in TableScripts)
                Console.WriteLine(script);
            /* If you want to create objects on the server you need call 
             * create method or else objects will not be created on the server */
            myDatabase.Create();
            myEmpTable.Create();
        }
    }
}
